package com.tgy.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Type;
import java.nio.charset.StandardCharsets;
import java.text.SimpleDateFormat;
import java.util.*;

import static org.apache.poi.ss.usermodel.CellType.*;

public class ExcelExportUtil {

    private static final int CELL_MIN_WEIGHT = 8;
    private static final int CELL_MAX_WEIGHT = 30;

    /**
     * 复制sheet页方法
     * @param wb        工作薄
     * @param sheetFrom 来源sheet
     * @param sheetTo   接受sheet
     */
    public static void copySheet(HSSFWorkbook wb, HSSFSheet sheetFrom, HSSFSheet sheetTo){
        //初始化
        CellRangeAddress region;
        Row rowFrom;
        Row rowTo;
        Cell cellFrom;
        Cell cellTo;

        for (int i = 0; i < sheetFrom.getNumMergedRegions(); i++) {
            region = sheetFrom.getMergedRegion(i);
            if ((region.getFirstColumn() >= sheetFrom.getFirstRowNum())
            && (region.getLastRow() <= sheetFrom.getLastRowNum())){
                sheetTo.addMergedRegion(region);
            }
        }

        //遍历行
        for (int intRow = sheetFrom.getFirstRowNum(); intRow <= sheetFrom.getLastRowNum(); intRow++) {
            rowFrom = sheetFrom.getRow(intRow);
            rowTo = sheetTo.createRow(intRow);
            if (null == rowFrom)
                continue;
            rowTo.setHeight(rowFrom.getHeight());

            //遍历列
            for (int intCol = 0; intCol < rowFrom.getLastCellNum(); intCol++) {
                HSSFCellStyle columnStyle = sheetFrom.getColumnStyle(intCol);
                if (null != columnStyle){
                    sheetTo.setDefaultColumnStyle(intCol,sheetFrom.getColumnStyle(intCol));
                }
                sheetTo.setColumnWidth(intCol,sheetFrom.getColumnWidth(intCol));
                cellFrom = rowFrom.getCell(intCol);
                cellTo = rowTo.createCell(intCol);
                if (null == cellFrom)
                    continue;

//                cellTo.setCellStyle(cellFrom.getCellStyle());         //这种方式设置样式只能两个cell是在同一个Workbook中，不同的要使用克隆的方式Workbook
                cellTo.getCellStyle().cloneStyleFrom(cellFrom.getCellStyle());
                FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

                CellReference cellReference = new CellReference(intRow,intCol);
                Row row = sheetFrom.getRow(cellReference.getRow());
                Cell cell = row.getCell(cellReference.getCol());
                CellValue cellValue = evaluator.evaluate(cell);

                copyCellValue(cellTo,cellValue);
            }
        }

        sheetTo.setDisplayGridlines(false);
        sheetTo.setZoom(100,100);   //比率，把前面个数写得特别小excel中就会等比缩小
    }
    /**
     * 给每一个cell赋值
     */
    private static void copyCellValue(Cell destCell, CellValue cellValue) {
        if (cellValue != null && destCell != null){
            switch (cellValue.getCellType()){
                case BOOLEAN:
                    destCell.setCellType(BOOLEAN);
                    destCell.setCellValue(cellValue.getBooleanValue());
                    break;
                case NUMERIC:
                    destCell.setCellType(NUMERIC);
                    destCell.setCellValue(cellValue.getNumberValue());
                    break;
                case STRING:
                    destCell.setCellType(STRING);
                    destCell.setCellValue(cellValue.getStringValue());
                    break;
                case BLANK:
                case FORMULA:
                    break;
                case ERROR:
                    destCell.setCellType(ERROR);
                    destCell.setCellValue(cellValue.getErrorValue());
                    break;
            }
        }
    }

    /**
     * 使用LinkedHashMap规定表头
     * @author: tianGuiYin
     * @date: 2021/12/2 11:03
     **/
    public static <T>HSSFWorkbook export(String sheetName,LinkedHashMap<String,String> map, List<T> dataList, Boolean isCreateSheet) throws IllegalAccessException{
        List<String> titleList = new ArrayList<>();
        List<String> fieldNames = new ArrayList<>();
        for (Map.Entry<String, String> stringStringEntry : map.entrySet()) {
            String key = stringStringEntry.getKey();
            String value = map.get(key);
            fieldNames.add(key);
            titleList.add(value);
        }
        String[] titleList1 = titleList.toArray(new String[0]);
        String[] fieldNames1 = fieldNames.toArray(new String[0]);
        return export(sheetName,titleList1,fieldNames1,dataList,isCreateSheet);
    }
    /**
     * 通用导出工具类
     * @param sheetName     sheet名称
     * @param titleList     表头集合
     * @param fieldNames    与表头对应的字段集合,必须和导出数据类中的字段名一致
     * @param dataList      导出数据集合
     * @param isCreateSheet 是否新建一个sheet页。true:新建，false:不新建
     * 使用示例
     * HSSFWorkbook hssfWorkbook = ExcelExportUtil.export(excel, "测试", titleList, fieldNames, list,false);
     * AmbDownloadContrller.downloadExcel(response,"测试",hssfWorkbook);
     */
    public static <T>HSSFWorkbook export(String sheetName, String[] titleList, String[] fieldNames, List<T> dataList, Boolean isCreateSheet) throws IllegalAccessException {
        Map<String,Integer> map = new HashMap<>();
        HSSFWorkbook workBook = new HSSFWorkbook();
        if (null == dataList){
            return workBook;
        }
        HSSFSheet sheet;
        if (!isCreateSheet){
            try {
                sheet = workBook.getSheetAt(0);
            }catch (Exception e){
                sheet =workBook.createSheet(sheetName);
            }
            workBook.setSheetName(0,sheetName);
        }else {
            sheet = workBook.createSheet(sheetName);
        }

        //设置表头
        HSSFRow titleRow = sheet.createRow(0);
        //表头样式
        HSSFCellStyle titleStyle = getTitleStyle(workBook);
        for (int i = 0; i < titleList.length; i++) {
            String titleValue = fieldNames[i];
            HSSFCell cell = titleRow.createCell(i);
            cell.setCellStyle(titleStyle);
            cell.setCellValue(titleList[i]);
            getValueLen(map, titleValue, returnWeightNumber(titleList[i]));
        }

        Class<?> aClass = dataList.get(0).getClass();
        Type type1 = aClass.getGenericSuperclass();
        String typeName = type1.getTypeName();
        System.out.println(typeName);
        //判断是否是Map封装的数据。map就不需要通过反射取值
        if (type1.equals(HashMap.class.getGenericSuperclass())){
            for (int i = 0; i < dataList.size(); i++) {
                HashMap<String,Object> data = (HashMap<String, Object>) dataList.get(i);
                HSSFRow row = sheet.createRow(i + 1);
                for (int j = 0; j < fieldNames.length; j++) {
                    HSSFCell cell = row.createCell(j);
                    String fieldName = fieldNames[j];
                    Object o = data.get(fieldName);
                    //设置单元格宽度
                    getValueLen(map, fieldName, returnWeightNumber(o));
                    //设置单元格数据
                    String type="";
                    if (o!=null){
                        type = o.getClass().toString();
                    }
                    setCellValue(type,o,cell);
                }
            }
        }else {
            //设置数据，不是Map封装的就通过反射取值
            for (int i = 0; i < dataList.size(); i++) {
                Object data = dataList.get(i);
                HSSFRow row = sheet.createRow(i + 1);
                for (int j = 0; j < fieldNames.length; j++) {
                    String fieldName = fieldNames[j];
                    HSSFCell cell = row.createCell(j);
                    //通过反射来获取 fieldName 字段对应的值
                    Field[] fields = data.getClass().getDeclaredFields();
                    for (Field field : fields) {
                        field.setAccessible(true);
                        String name = field.getName();
                        Object o = field.get(data);
                        String type = field.getType().toString();      //class java.lang.String
                        if (name.equals(fieldName)){
                            //设置单元格宽度
                            getValueLen(map, fieldName, returnWeightNumber(o));
                            //设置单元格数据
                            setCellValue(type,o,cell);
                            break;
                        }
                    }
                }
            }
        }

        //自适应单元格宽度
        for (int i = 0; i < fieldNames.length; i++) {
            Integer integer = map.get(fieldNames[i]) == null ? CELL_MIN_WEIGHT : map.get(fieldNames[i]);
            sheet.setColumnWidth(i,integer*256);
        }

        return workBook;
    }

    /**
     * 设置表头样式
     * @author: tianGuiYin
     * @date: 2021/12/2 10:24
     **/
    public static HSSFCellStyle getTitleStyle(HSSFWorkbook workBook){
        HSSFCellStyle titleStyle = workBook.createCellStyle();
        HSSFFont titleFont = workBook.createFont();
//        titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  //宽度
        titleFont.setFontHeightInPoints((short)12);     //字号
        titleStyle.setFont(titleFont);
//        titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
//        titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
//        titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//        titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);        //水平居中
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);     //垂直居中
        titleStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());    //背景颜色，必须设置以下两行代码
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//        titleStyle.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        titleStyle.setWrapText(true);       //自动换行

        return titleStyle;
    }

    /**
     * 给单元格赋值（没考虑到的数据类型后续自己增加）
     */
    private static void setCellValue(String type, Object value, HSSFCell cell){
        if (null == value){
            cell.setCellType(STRING);
            cell.setCellValue("");
        }else
        if ("class java.lang.String".equalsIgnoreCase(type) || "string".equalsIgnoreCase(type)) {
            cell.setCellType(STRING);
            cell.setCellValue((String) value);
        }else
        if ("class java.lang.Long".equalsIgnoreCase(type) || "long".equalsIgnoreCase(type)){
            cell.setCellType(STRING);
            cell.setCellValue((Long) value);
        }else
        if ("class java.lang.Integer".equalsIgnoreCase(type) || "class java.lang.Double".equalsIgnoreCase(type) || "int".equalsIgnoreCase(type) || "double".equalsIgnoreCase(type)) {
            cell.setCellType(NUMERIC);
            if (value instanceof Integer){
                cell.setCellValue((Integer)value);
            }else if (value instanceof Double){
                cell.setCellValue((Double)value);
            }
        }else
        if ("class java.math.BigDecimal".equalsIgnoreCase(type)) {
            cell.setCellType(STRING);
            cell.setCellValue(value.toString());
        }else
        if ("class java.util.Date".equalsIgnoreCase(type)) {
            String date = new SimpleDateFormat("yyyy-MM-dd").format(value);
            cell.setCellType(STRING);
            cell.setCellValue(date);
        }
    }

    /**
     * 计算单元格的宽度
     */
    private static void getValueLen(Map<String, Integer> map, String titleValue, int strLen) {
        Integer integer = map.get(titleValue);
        if (null==integer){
            map.put(titleValue,strLen);
        }else if (strLen>integer){
            map.put(titleValue,strLen);
        }
    }

    /**
     * 根据内容信息计算单元格宽度
     */
    private static int returnWeightNumber(Object object){
        if (null == object){
            return CELL_MIN_WEIGHT;
        }
        String value;
        if (object instanceof Date){
            value = new SimpleDateFormat("yyyy-MM-dd").format(object);
        }else {
            value = object.toString();
        }
        int byteLen = value.getBytes().length;  //字节长度
        //字节长度-字符串长度就是中文个数
        int chineseNumber = byteLen - value.length();
        //将中文个数+字符串个数+2为单元格宽度
        int weight = chineseNumber+value.length()+2;
        if (weight<CELL_MIN_WEIGHT){
            weight = CELL_MIN_WEIGHT;
        }else if (weight>CELL_MAX_WEIGHT){
            weight = CELL_MAX_WEIGHT;
        }
        return weight;
    }

    /**
     * 指定增加一行
     * @author: tianGuiYin
     * @date: 2021/12/2 10:14
     **/
    public static void addRow(HSSFWorkbook wb,String sheetName,int createNewRowAt){
        HSSFSheet sheet = wb.getSheet(sheetName);
//        HSSFRow newRow;
        sheet.shiftRows(createNewRowAt,sheet.getLastRowNum(),1,true,false);
//        newRow = sheet.createRow(createNewRowAt);
        sheet.createRow(createNewRowAt);
    }

    /**
     * 下载Excel用于单独书写的export方法
     * @param response          response
     * @param fileName          文件名称
     * @param hssfWorkbook      HSSFWorkbook
     */
    public static void downloadExcel(HttpServletResponse response, String fileName, HSSFWorkbook hssfWorkbook)
            throws IOException {
        //生成并下载excel文件
        ServletOutputStream sos = null;
        try {
            fileName = fileName+".xls";
            response.setContentType("application/octe-stream");
            response.setCharacterEncoding("utf-8");
            response.addHeader("Content-Disposition", "attachment;filename="+new String(fileName.getBytes(), StandardCharsets.ISO_8859_1));
            sos = response.getOutputStream();
            hssfWorkbook.write(sos);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }finally{
            if(sos!=null){
                sos.flush();
                sos.close();
            }
        }
    }

}
